package com.correo.database.server;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;

import com.correo.database.beans.CasillaCorreoBean;
import com.correo.database.beans.Configuracion;
import com.correo.database.beans.IBean;
import com.correo.database.beans.SessionBean;
import com.correo.database.beans.UserBean;
import com.correo.socket.client.beans.LoginBean;

public class ManagerDataBaseUser implements IManagerDataBase{

	private Connection connection;
	public ManagerDataBaseUser(Connection connection) {
		this.connection = connection;
	}
	public void alta(IBean dataBean) throws SQLException {

		String strQuery="{CALL usp_userDataInsert(?,?,?,?,?,?,?)}";
		CallableStatement callStm=getConnection().prepareCall(strQuery);
		callStm.setString( 1,((UserBean)dataBean).getId());
		callStm.setString( 2,((UserBean)dataBean).getApellido());
		callStm.setString( 3,null);
		callStm.setString( 4,null);
		callStm.setString( 5,null);
		callStm.setString( 6,null);
		callStm.setString( 7,((UserBean)dataBean).getPasword());
		
		callStm.execute();
		getConnection().commit();
		callStm.close();
		agregar(((UserBean)dataBean).getId().trim(), ((UserBean)dataBean).getPasword().trim());
	}
	public Connection getConnection() {
		return connection;
	}
	public void agregar(String user, String password) {
		
		
		Statement stmt;
        String sentenciaLogin = "CREATE LOGIN ["+user+ "] WITH PASSWORD= '" +password+ "' , DEFAULT_DATABASE=["+Configuracion.DATABASE+"], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF ";
        String sentenciaUsuario = "CREATE USER ["+user+"] FOR LOGIN ["+user+"] ";
        String sentenciaPermiso = "Grant EXECUTE to ["+user+"]";
        String sentencia = sentenciaLogin + sentenciaUsuario + sentenciaPermiso;
        try {
			
        	stmt = getConnection().createStatement();
			stmt.execute(sentencia);
			getConnection().commit();
			stmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
        
	}
	
	public void eliminar(String user) {
		
		
		Statement stmt;
        String dropLogin = "DROP LOGIN ["+user+ "] ";
        String dropUsuario = "DROP USER ["+user+"] ";
        String sentencia = dropLogin + dropUsuario;
        try {
			
        	stmt = getConnection().createStatement();
			stmt.execute(sentencia);
			getConnection().commit();
			stmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
        
	}
	
	public void cambiarEstadoLogin(String login,boolean habilitado)
	{
		String shabilitado = habilitado?"ENABLE":"DISABLE";
		Statement stmt;
        String sentencia = "ALTER LOGIN ["+login+ "] " + shabilitado;
       
        try {
			
        	stmt = getConnection().createStatement();
			stmt.execute(sentencia);
			getConnection().commit();
			stmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	
	public List<IBean> refresh() throws SQLException {
		List<IBean> lista = new LinkedList<IBean>();
		String strQuery="{CALL usp_userDataSelect(?)}";
		CallableStatement stm = getConnection().prepareCall(strQuery);
		stm.setString(1, null);
		ResultSet result = stm.executeQuery();
		UserBean user=null;
		while(result.next()){
			user = new UserBean();
			user.setId(result.getString("userId"));
			user.setApellido(result.getString("apellido"));
			user.setPasword(result.getString("password"));
			lista.add(user);
		}
		stm.close();
		//obtengo las casillas asociadas a cada usuario
		Iterator<IBean> it=lista.iterator();
		CasillaCorreoBean casilla;
		while(it.hasNext()){
			user = (UserBean) it.next();
			strQuery="{CALL usp_casillaCorreoSelect(?,?)}";
			stm = getConnection().prepareCall(strQuery);
			stm.setString(1, null);
			stm.setString(2, user.getId());
			result = stm.executeQuery();
			while (result.next()) {
				casilla = new CasillaCorreoBean();
				casilla.setId(result.getString("casillaCorreoId"));
				user.getListaCasillas().add(casilla);
			}
			stm.close();
		}
		return lista;
	}
	public void remove(List<IBean> listBean) throws SQLException {
		if(listBean!=null){
			Iterator<IBean> it =listBean.iterator();
			CallableStatement stm=null;
			String strQuery="{CALL usp_userDataDelete(?)}";
			UserBean user=null;
			while(it.hasNext()){
				user = (UserBean) it.next();
				stm = getConnection().prepareCall(strQuery);
				stm.setString(1, user.getId());
				stm.executeUpdate();
				getConnection().commit();
				stm.close();
				eliminar(user.getId().trim());
			}
			
		}
	}
	public void modify(IBean bean) throws SQLException {
		String strQuery="{CALL usp_userDataUpdate(?,?,?,?,?,?,?)}";
		CallableStatement callStm=getConnection().prepareCall(strQuery);
		callStm.setString( 1,((UserBean)bean).getId());
		callStm.setString( 2,((UserBean)bean).getApellido());
		callStm.setString( 3,null);
		callStm.setString( 4,null);
		callStm.setString( 5,null);
		callStm.setString( 6,null);
		callStm.setString( 7,((UserBean)bean).getPasword());
		
		callStm.execute();
		getConnection().commit();
		callStm.close();
		eliminar(((UserBean)bean).getId());
		agregar(((UserBean)bean).getId().trim(),((UserBean)bean).getPasword().trim());
	}
	
	public boolean verificarUsuario (LoginBean login) 
	{
		String strQuery="{CALL usp_userDataValidate(?,?)}";
		CallableStatement stm;
		boolean resultado = false;
		try {
			stm = getConnection().prepareCall(strQuery);
			stm.setString(1, login.getUsuario());
			stm.setString(2,login.getPassword());
			ResultSet result = stm.executeQuery();
			
			 if ( result.next())
			 {
				 resultado = true;
			 }
			 stm.close();

		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		
		
		return resultado;
	}
	
	public void altaSession(SessionBean session) throws SQLException {

		String strQuery="{CALL usp_SessionesInsert(?,?,?,?)}";
		CallableStatement callStm=getConnection().prepareCall(strQuery);
		callStm.setString( 1,session.getChallenge());
		callStm.setString( 2,session.getUser());
		callStm.setDate(3, session.getCreateDate());
		callStm.setString( 4,session.getIp());
		
		
		callStm.execute();
		getConnection().commit();
		callStm.close();
		
	}
	
	public boolean verificarSessionesAnteriores (String challenge) 
	{
		String strQuery="{CALL usp_SessionesSelect(?)}";
		CallableStatement stm;
		boolean resultado = false;
		try {
			stm = getConnection().prepareCall(strQuery);
			stm.setString(1, challenge);
			ResultSet result = stm.executeQuery();
			
			 if ( result.next())
			 {
				 resultado = true;
			 }
			 stm.close();

		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		
		
		return resultado;
	}

}
